require 'mysql2'
require 'time'
require 'json'
require 'awesome_print'
require 'spreadsheet' #用于写xls文件
require 'simple-spreadsheet' #用于读取xlsx文件，spreadsheet gem读xlsx会报签名错误
require 'os'

@tmall = Mysql2::Client.new({ host: ENV['RDS_AGENT'], username: 'psi_root', port: 1403, password: ENV['PSI_PASSWORD'], database: 'tutem_mall' })
@rds_fc = Mysql2::Client.new(:encoding => 'utf8mb4', :host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1403', :password => ENV['PSI_PASSWORD'])
@rds_tt = Mysql2::Client.new(:encoding => 'utf8mb4', :host => ENV['RDS_AGENT'], :username => "psi_root", :port => '1403', :password => ENV['PSI_PASSWORD'])
#@rds_fcdev = Mysql2::Client.new(:encoding => 'utf8mb4', :host => "dev.foodtrust.cn", :username => "psi_root", :port => '3306', :password => ENV['DEV_PASSWORD'])

@rds_mode = 'normal'
@rds = @rds_fc

def append_line_to_log line, fn
    dir = OS.windows? ? ".\\log\\" : "./log/"
    File.open(dir + fn,"a:UTF-8") { |f| f.write line }
end

def commitTrans inqs
    @rds.query('begin')
    inqs.each do |inq|
        puts inq if @rds_mode.include? 'debug'
        begin
            @rds.query(inq)
        rescue => e
            @rds.query('rollback')
            error_msg =  ">>>ERROR:#{Time.now.to_s} #{e}\n   SQL: #{inq}\n"
            append_line_to_log error_msg, "rds_api.log"
            return false
        end
    end
    @rds.query('commit')
    return true
end

def queryRds inq
    puts inq if @rds_mode.include? 'debug'
    begin
        return @rds.query(inq)
    rescue => e
        puts ">>>ERROR: #{e}\n   SQL: #{inq}"
        error_msg =  ">>>ERROR:#{Time.now.to_s} #{e}\n   SQL: #{inq}"
        append_line_to_log error_msg, "rds_api.log"
    end
end

#这个是数组
def save_items_to_excel list, fn
    return if items.size == 0
    example = items[0]
    headers = example.keys
    names = example.keys
    save_to_excel headers, names, items, fn
end

#这个是Hash
def save_list_to_excel list, fn
    return if list.size == 0
    example = {}
    items = []
    list.each do |key, item|
        example = item if example == {}
        items += [item]
    end
    headers = example.keys
    names = example.keys
    save_to_excel headers, names, items, fn
end

def save_to_excel headers, names, items, fn

    Spreadsheet.client_encoding="utf-8"
    book = Spreadsheet::Workbook.new
    sheet = book.create_worksheet :name => "sheet"

    #生成表头
    colIndex=0 #col index
    headers.each do |header|
        sheet.row(0)[colIndex]=header
        colIndex += 1
    end

    lineIndex = 1
    items.each do |item|
        colIndex=0
        names.each do |name|
            val = item[name]
            case item[name].class.to_s
            when 'Integer', 'String'
                val = val
            when 'BigDecimal', 'Float'
                val = val.to_f.round(2)
            else
                val = val.to_json
            end
            sheet.row(lineIndex)[colIndex] = val
            colIndex +=1
        end
        lineIndex += 1
    end

    book.write fn
    #puts "#{fn} saved: #{items.size}"
end

def unixtime_of_sday sday
    #stime = ( Time.parse sday.strftime('%Y-%m-%d') + " 00:00:00" ).strftime('%Y-%m-%d %H:%M:%S')
    stime = ( Time.parse sday.strftime('%Y-%m-%d') + " 00:00:00" ).to_i
    return stime
end

def unixtime_of_eday eday
    #etime = ( Time.parse eday.strftime('%Y-%m-%d') + " 23:59:59" ).strftime('%Y-%m-%d %H:%M:%S')
    etime = ( Time.parse eday.strftime('%Y-%m-%d') + " 23:59:59" ).to_i
    return etime
end

def barcodeList
    return @abcl if @abcl
    @abcl = {}
    sql = "select pav_unique, pav_barcode from tutem_mall.eb_unify_pcode"
    records = executeRds(sql)
    records.each do |rec|
        input_string = rec['pav_barcode']
        pattern = /^(\w+-\w+)-\w+$/       #将TTYS-16-231111截为TTYS-16
        match = input_string.match(pattern)
        barcode = match ? match[1] : rec['pav_barcode']
        @abcl.store  rec['pav_unique'], barcode
    end
    return @abcl
end

def skuList
    return @asl if @asl
    @asl = {}
    sql = "select pav_unique, pav_sku from tutem_mall.eb_unify_pcode"
    records = executeRds(sql)
    records.each do |rec|
        @asl.store  rec['pav_unique'], rec['pav_sku']
    end
    return @asl
end

def getUserListByUids uids
    recs = {}
    uids.each do |uid|
        recs.store uid, userList[uid]
    end
    return recs
end

def promoterList

    return @promoterList if @promoterList
    @promoterList = {}
    sql = "select u.* from tutem_mall.eb_user u where is_promoter = 1" #所有promoters
    (queryRds(sql)).each { |q| @promoterList.store q['uid'], q }
    return @promoterList

end

def professionalList

    if @rds != @rds_tt
        puts "ERROR: only can be called for rds_tt"
        return {}
    end

    return @professionals if @professionals
    @professionals = {}

    #item = { "uid" => 0, "user_name" => 'sys', 'agency' => 'totembio' }
    #@professionals.store 0, item

    db_name = @rds == @rds_fc ? 'crmeb' : 'tutem_mall'
    sql = "select u.uid, u.real_name as user_name, u.agency from #{db_name}.eb_user u where is_promoter = 1 and promoter_type = 1 or promoter_type = 3" #只包括医生和员工
    (queryRds(sql)).each { |q| @professionals.store q['uid'], q }

    return @professionals

end

def systemList

    if @rds != @rds_tt
        puts "ERROR: only can be called for rds_tt"
        return {}
    end

    return @systems if @systems
    @systems = {}

    item = { 'uid' => 0, 'user_name' => 'system', 'agency' => 'totembio' }
    @systems.store 0, item

    db_name = @rds == @rds_fc ? 'crmeb' : 'tutem_mall'
    sql = "select u.uid, u.real_name as user_name, u.agency from #{db_name}.eb_user u where is_promoter = 1 and  promoter_type = 4"
    (queryRds(sql)).each { |q| @systems.store q['uid'], q }

    return @systems

end

def userList

    return @users if @users

    @users = {}

    #为uid 0建立虚拟数据
    item =  {
        "uid" => 0,
        "user_name" => 'sys', "real_name" => 'sys', "nickname" => 'sys', "order_contact" => 'sys', "name" => 'sys', "aname" => 'sys',
        "order_phone" => '13600060000', "phone" => '13600060000', "aphone" => '13600060000',
        "user_agency" => 'totembio', "agency" => 'totem', "department" => 'life',
        "is_promoter" => 1, "promoter_type" => 1,
        "spreader_uid" => 0, "spread_uid" => 0, "spreader_name" => 'root', "spreader_type" => 3,
        "order_city" => '',
        "now_money" => 0.0,
        "login_type" => '',
        "is_qp" => 0,
        "rep_id" => 0, "rep_aid" => 0, "rep_uid" => 0, "rep_code" => 'UNS',
        "purchased" => '', "pay_count" => 0, "last_purchased" => '', "goods_list" => '', "profile" => '',
        "mp_subscribed" => 0, "status" => 1,
        "external_userid" => '', "wework_external_usreid" => '', "follow_userid" => '' 
    }
    @users.store 0, item

    db_name = @rds == @rds_fc ? 'crmeb' : 'tutem_mall'
    sql = "SELECT u.uid, u.status, u.real_name, u.purchased, u.last_purchased, u.goods_list, u.profile, u.agency, u.department,
        u.is_promoter, u.promoter_type, u.pay_count, u.nickname, u.phone, u.pay_count, u.now_money,
        u.rep_id, u.rep_code, u.spread_uid, u.spread_uid as spreader_uid,
        s.real_name as spreader_name, s.promoter_type as spreader_type,
        u.login_type, u.is_qp,
        w.subscribe as mp_subscribed, w.external_userid, c.follow_info_userid as follow_userid,
        d.phone as order_phone, d.city as order_city, d.real_name as order_contact
        FROM #{db_name}.eb_user u left join #{db_name}.eb_user s on u.spread_uid = s.uid
        left join #{db_name}.eb_wechat_user w on u.uid = w.uid
        left join #{db_name}.eb_user_address d on u.uid = d.uid
        left join #{db_name}.eb_wework_external_contact c on c.external_userid = w.external_userid;"

    queryRds(sql).each do |q|

        uid = q['uid']

        real_name = q['real_name'].to_s
        nickname = q['nickname'].to_s
        order_contact = q['order_contact'].to_s
        name = real_name
        name = order_contact if name.to_s.empty?
        name = nickname if name.to_s.empty?
        aname = name.length < 1 ? '****' : name #这些axxxx是为了做匿名显示的
        aname = aname[0] + '**'

        phone = q['phone'].to_s
        order_phone = q['order_phone'].to_s
        phone = order_phone if phone.empty?
        aphone = phone.length < 4 ? '******' : phone #这些axxxx是为了做匿名显示的
        aphone = '******' + aphone[aphone.length-4..aphone.length-1]

        agency = q['agency'].to_s
        department = q['department'].to_s
        is_promoter = q['is_promoter']
        promoter_type = q['promoter_type']
        spreader_uid = q['spreader_uid']
        spreader_name = q['spreader_name'].to_s
        spreader_type = q['spreader_type'].nil? ? 3 : q['spreader_type']

        order_city = q['order_city'].to_s
        now_money = q['now_money'].to_f.round(2)

        login_type = q['login_type']
        is_qp = q['is_qp']

        rep_id = q['rep_id']
        rep_aid = rep_id
        rep_uid = 0 #lastr will add rep management TBO

        rep_code = q['rep_code']

        purchased = q['purchased']
        goods_list = q['goods_list']
        pay_count = q['pay_count']
        last_purchased = q['last_purchased']
        profile = q['profile']

        external_userid = q['external_userid'] #用户自己的企业微信ID
        follow_userid = q['follow_userid'].to_s #对应用户企业微信的客服ID

        mp_subscribed = q['mp_subscribed']
        status = q['status']

        item =  {
            "uid" => uid,
            "user_name" => name, "real_name" => real_name, "nickname" => nickname, "order_contact" => order_contact, "name" => name, "aname" => aname,
            "order_phone" => order_phone, "phone" => phone, "aphone" => aphone,
            "user_agency" => agency, "agency" => agency, "department" => department,
            "is_promoter" => is_promoter, "promoter_type" => promoter_type,
            "spreader_uid" => spreader_uid, "spread_uid" => spreader_uid, "spreader_name" => spreader_name, "spreader_type" => spreader_type,
            "order_city" => order_city,
            "now_money" => now_money,
            "login_type" => login_type,
            "is_qp" => is_qp,
            "rep_id" => rep_id, "rep_aid" => rep_id, "rep_uid" => rep_uid, "rep_code" => rep_code,
            "purchased" => purchased, "pay_count" => pay_count, "last_purchased" => last_purchased, "goods_list" => goods_list, "profile" => profile,
            "mp_subscribed" => mp_subscribed, "status" => status,
            "external_userid" => external_userid, "wework_external_usreid" => external_userid , "follow_userid" => follow_userid
        }

        @users.store q['uid'], item

    end
    return @users
end

def recs_to_excel recs, fn

    return if recs.size < 1

    items = []
    recs.each do |k, v|
        items += [v]
    end

    item = items[0]
    headers = []
    names = []
    item.each do |k, v|
        headers += [k]
        names += [k]
    end

    save_to_excel headers, names, items, fn

end

def get_xlsx_recs_from xlsx, fields

    puts "reading #{xlsx}..."
    recs = []

    s = SimpleSpreadsheet::Workbook.read xlsx
    s.selected_sheet = s.sheets.first
    line_idx = 0

    s.first_row.upto(s.last_row) do |line|

        rec = {}
        line_idx += 1
        next if line_idx == 1

        #根据field读取到
        idx = 0
        fields.each do |f|
          idx += 1
          v = s.cell(line,idx)
          v = v.to_s if v.nil?
          rec.store f, v
        end

        recs += [rec]

    end

    puts "done"
    return recs

end

def get_hash_from_titled_xlsx xlsx

    hash = {}
    fields = []

    s = SimpleSpreadsheet::Workbook.read xlsx
    s.selected_sheet = s.sheets.first
    line_idx = 0

    s.first_row.upto(s.last_row) do |line|

        if line_idx == 0

            idx = 0
            filed_not_emptyed = true
            while filed_not_emptyed && idx < 100
                field = s.cell(line, idx).to_s
                idx += 1
                if field.empty?
                    field_not_emptyed = false
                else
                    fields += [field]
                end
            end

        else

            #根据field读取到rec
            rec = {}
            idx = 0
            fields.each do |f|
              idx += 1
              v = s.cell(line,idx)
              v = v.to_s if v.nil?
              rec.store f, v
            end

            hash.store line_idx, rec

        end

        line_idx += 1

    end

    return hash

end

def dump_array_to_excel recs, fn = 'array_info', auto_append = true
    return if recs.class != Array || recs.size == 0
    samples = recs[0]
    headers = samples.keys
    names = samples.keys
    date = Time.now.to_i.to_s
    fn = "#{fn}-#{date}.xls" if auto_append
    save_to_excel headers, names, recs, fn
end

def dump_hash_to_excel h, fn = 'hash_info', auto_append = true #如果不附加则只用
    return false if h.class != Hash || h.size == 0
    samples = h.first[1]
    headers = samples.keys
    names = samples.keys
    date = Time.now.to_s.split(' ')[0]
    file = fn
    file = "#{fn}-#{date}.xls" if auto_append
    recs = []
    h.each { |k, v| recs += [v] }
    save_to_excel headers, names, recs, file
    return true
end

def executeRds sql, *params
    statement = @rds.prepare(sql)
    results = statement.execute(*params)
end
#executeRds('SELECT * FROM tutem_mall.eb_user where uid = ?', 1);
